package com.dxd.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dxd.entity.Message;
import com.dxd.entity.MessageType;
import com.dxd.entity.PageEntity;
import com.dxd.entity.UserInfo;

public class MessageDao extends PublicDao<Message> {

	@Override
	protected Message result(ResultSet rs) throws SQLException {
		Message message = new Message();
		while (rs.next()) {
			message.setId(rs.getInt("id"));
			message.setAddress(rs.getString("address"));
			message.setCreateTime(rs.getString("create_time"));
			message.setDetailContent(rs.getString("detail_content"));
			message.setImgs(rs.getString("imgs"));
			message.setLostTime(rs.getString("lost_time"));
			message.setState(rs.getInt("state"));
			message.setTitle(rs.getString("title"));
			UserInfo userInfo = new UserInfo();
			userInfo.setId(rs.getInt("u_id"));
			userInfo.setUserName(rs.getString("user_name"));
			userInfo.setPassWord(rs.getString("pass_word"));
			userInfo.setName(rs.getString("u_name"));
			userInfo.setSex(rs.getString("sex"));
			userInfo.setHeadImg(rs.getString("head_img"));
			userInfo.setPhone(rs.getString("phone"));
			userInfo.setAddress(rs.getString("u_address"));
			userInfo.setCreateTime(rs.getString("u_create_time"));
			userInfo.setIsAdmin(rs.getInt("is_admin"));
			userInfo.setState(rs.getInt("u_state"));
			userInfo.setProvince(rs.getString("province"));
			userInfo.setCity(rs.getString("city"));
			userInfo.setArea(rs.getString("area"));
			userInfo.setAge(rs.getInt("age"));
			message.setCreateUse(userInfo);
			MessageType messageType = new MessageType();
			messageType.setId(rs.getInt("t_id"));
			messageType.setName(rs.getString("t_name"));
			MessageType parentmessageType = new MessageType();
			parentmessageType.setId(rs.getInt("p_id"));
			parentmessageType.setName(rs.getString("p_name"));
			parentmessageType.setType(rs.getInt("p_type"));
			parentmessageType.setCreateTime(rs.getString("p_create_time"));
			messageType.setType(rs.getInt("type"));
			messageType.setParent(parentmessageType);
			messageType.setCreateTime(rs.getString("t_create_time"));
			message.setMessageType(messageType);
		}
		return message;
	}

	@Override
	protected List<Message> results(ResultSet rs) throws SQLException {
		List<Message> list = new ArrayList<Message>();
		while (rs.next()) {
			Message message = new Message();
			message.setId(rs.getInt("id"));
			message.setAddress(rs.getString("address"));
			message.setCreateTime(rs.getString("create_time"));
			message.setDetailContent(rs.getString("detail_content"));
			message.setImgs(rs.getString("imgs"));
			message.setLostTime(rs.getString("lost_time"));
			message.setState(rs.getInt("state"));
			message.setTitle(rs.getString("title"));
			UserInfo userInfo = new UserInfo();
			userInfo.setId(rs.getInt("u_id"));
			userInfo.setUserName(rs.getString("user_name"));
			userInfo.setPassWord(rs.getString("pass_word"));
			userInfo.setName(rs.getString("u_name"));
			userInfo.setSex(rs.getString("sex"));
			userInfo.setHeadImg(rs.getString("head_img"));
			userInfo.setPhone(rs.getString("phone"));
			userInfo.setAddress(rs.getString("u_address"));
			userInfo.setCreateTime(rs.getString("u_create_time"));
			userInfo.setIsAdmin(rs.getInt("is_admin"));
			userInfo.setState(rs.getInt("u_state"));
			userInfo.setProvince(rs.getString("province"));
			userInfo.setCity(rs.getString("city"));
			userInfo.setArea(rs.getString("area"));
			userInfo.setAge(rs.getInt("age"));
			message.setCreateUse(userInfo);
			MessageType messageType = new MessageType();
			messageType.setId(rs.getInt("t_id"));
			messageType.setName(rs.getString("t_name"));
			MessageType parentmessageType = new MessageType();
			parentmessageType.setId(rs.getInt("p_id"));
			parentmessageType.setName(rs.getString("p_name"));
			parentmessageType.setType(rs.getInt("p_type"));
			parentmessageType.setCreateTime(rs.getString("p_create_time"));
			messageType.setType(rs.getInt("type"));
			messageType.setParent(parentmessageType);
			messageType.setCreateTime(rs.getString("t_create_time"));
			message.setMessageType(messageType);
			list.add(message);
			message = null;
		}
		return list;
	}

	/**
	 * 后端查询所有的消息
	 * 
	 * @param page
	 * @param size
	 * @param title
	 * @param typeName
	 * @return
	 */
	public PageEntity<Message> queryList(int page, int size, String title, String typeName) {
		PageEntity<Message> pageEntity = new PageEntity<Message>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("m.id,");
		sql.append("m.address,");
		sql.append("m.create_time,");
		sql.append("m.detail_content,");
		sql.append("m.imgs,");
		sql.append("m.lost_time,");
		sql.append("m.state,");
		sql.append("m.title,");
		sql.append("u.id AS u_id,");
		sql.append("u.user_name,");
		sql.append("u.pass_word,");
		sql.append("u.`name` AS u_name,");
		sql.append("u.sex,");
		sql.append("u.head_img,");
		sql.append("u.phone,");
		sql.append("u.address AS u_address,");
		sql.append("u.create_time AS u_create_time,");
		sql.append("u.is_admin,");
		sql.append("u.state AS u_state,");
		sql.append("u.province,");
		sql.append("u.city,");
		sql.append("u.area,");
		sql.append("u.age,");
		sql.append("mt.id AS t_id,");
		sql.append("mt.`name` AS t_name,");
		sql.append("pmt.id AS p_id,");
		sql.append("pmt.`name` AS p_name,");
		sql.append("pmt.type AS p_type,");
		sql.append("pmt.create_time AS p_create_time,");
		sql.append("mt.type,");
		sql.append("mt.create_time as t_create_time ");
		sql.append(
				"from message m LEFT JOIN  message_type mt ON m.message_type=mt.id LEFT JOIN message_type pmt on mt.parent_id=pmt.id LEFT JOIN user_info u on m.create_user=u.id where 1=1");
		StringBuffer countsql = new StringBuffer(
				"SELECT COUNT(1) as count FROM message m LEFT JOIN  message_type mt ON m.message_type=mt.id LEFT JOIN message_type pmt on mt.parent_id=pmt.id LEFT JOIN user_info u on m.create_user=u.id where 1=1 ");
		if (title != null && !"".equals(title)) {
			sql.append(" and m.title like'%");
			sql.append(title);
			sql.append("%'");
			countsql.append(" and m.title like'%");
			countsql.append(title);
			countsql.append("%'");

		}
		if (typeName != null && !"".equals(typeName)) {
			sql.append(" and mt.name like '%");
			sql.append(typeName);
			sql.append("%'");
			countsql.append(" and mt.name like '%");
			countsql.append(typeName);
			countsql.append("%'");
		}
		int count = queryCount(countsql.toString(), null);
		sql.append(" LIMIT ");
		sql.append((page - 1) * size);
		sql.append(",");
		sql.append(size);
		List<Message> list = queryList(sql.toString(), null);
		pageEntity.setPage(page);
		pageEntity.setSize(size);
		pageEntity.setList(list);
		pageEntity.setTotalCount(count);

		return pageEntity;

	}

	/**
	 * 前端查询所有消息
	 * 
	 * @param page
	 * @param size
	 * @param typeParentId
	 * @param typeName
	 * @param searchName
	 * @param lostTime
	 * @return
	 */
	public PageEntity<Message> queryList(int page, int size, Integer typeParentId, String typeName, String searchName,
			String lostTime, Integer userId) {
		PageEntity<Message> pageEntity = new PageEntity<Message>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("m.id,");
		sql.append("m.address,");
		sql.append("m.create_time,");
		sql.append("m.detail_content,");
		sql.append("m.imgs,");
		sql.append("m.lost_time,");
		sql.append("m.state,");
		sql.append("m.title,");
		sql.append("u.id AS u_id,");
		sql.append("u.user_name,");
		sql.append("u.pass_word,");
		sql.append("u.`name` AS u_name,");
		sql.append("u.sex,");
		sql.append("u.head_img,");
		sql.append("u.phone,");
		sql.append("u.address AS u_address,");
		sql.append("u.create_time AS u_create_time,");
		sql.append("u.is_admin,");
		sql.append("u.state AS u_state,");
		sql.append("u.province,");
		sql.append("u.city,");
		sql.append("u.area,");
		sql.append("u.age,");
		sql.append("mt.id AS t_id,");
		sql.append("mt.`name` AS t_name,");
		sql.append("pmt.id AS p_id,");
		sql.append("pmt.`name` AS p_name,");
		sql.append("pmt.type AS p_type,");
		sql.append("pmt.create_time AS p_create_time,");
		sql.append("mt.type,");
		sql.append("mt.create_time as t_create_time ");
		sql.append(
				"from message m LEFT JOIN  message_type mt ON m.message_type=mt.id LEFT JOIN message_type pmt on mt.parent_id=pmt.id LEFT JOIN user_info u on m.create_user=u.id where 1=1");
		StringBuffer countsql = new StringBuffer(
				"SELECT COUNT(1) as count FROM message m LEFT JOIN  message_type mt ON m.message_type=mt.id LEFT JOIN message_type pmt on mt.parent_id=pmt.id LEFT JOIN user_info u on m.create_user=u.id where 1=1 ");

		if (searchName != null && !"".equals(searchName)) {
			sql.append(" and (");

			sql.append(" m.title like'%");
			sql.append(searchName);
			sql.append("%'");

			sql.append("or m.address like '%");
			sql.append(searchName);
			sql.append("%'");

			sql.append("or m.detail_content like '%");
			sql.append(searchName);
			sql.append("%'");

			sql.append(" )");

			countsql.append(" and (");

			countsql.append(" m.title like'%");
			countsql.append(searchName);
			countsql.append("%'");

			countsql.append("or m.address like '%");
			countsql.append(searchName);
			countsql.append("%'");

			countsql.append("or m.detail_content like '%");
			countsql.append(searchName);
			countsql.append("%'");
			countsql.append(" )");

		}
		if (typeParentId != null) {
			sql.append(" and pmt.id=");
			sql.append(typeParentId);
			countsql.append(" and pmt.id=");
			countsql.append(typeParentId);
		}
		if (typeName != null && !"".equals(typeName)) {
			sql.append(" and mt.name='");
			sql.append(typeName);
			sql.append("'");
			countsql.append(" and mt.name='");
			countsql.append(typeName);
			countsql.append("'");
		}
		if (userId != null) {
			sql.append(" and m.create_user=");
			sql.append(userId);
			countsql.append(" and m.create_user=");
			countsql.append(userId);

		}else{
			sql.append(" and m.state not in (0,3,4)");
			countsql.append(" and m.state not in (0,3,4)");
		}
		if (lostTime != null && !"".equals(lostTime)) {
			if ("1".equals(lostTime)) {// 查询今天的
				sql.append(" and (TO_DAYS( now() ) - TO_DAYS(m.create_time)=0)");
				countsql.append(" and (TO_DAYS( now() ) - TO_DAYS(m.create_time)=0)");
			} else if ("2".equals(lostTime)) {// 三天内的
				sql.append(" and DATEDIFF(NOW(),m.create_time)<=3 and DATEDIFF(NOW(),m.create_time)>=0");
				countsql.append(" and DATEDIFF(NOW(),m.create_time)<=3 and DATEDIFF(NOW(),m.create_time)>=0");
			} else if ("3".equals(lostTime)) {// 7天内的
				sql.append(" and DATEDIFF(NOW(),m.create_time)<=7 and DATEDIFF(NOW(),m.create_time)>=0");
				countsql.append(" and DATEDIFF(NOW(),m.create_time)<=7 and DATEDIFF(NOW(),m.create_time)>=0");
			} else if ("4".equals(lostTime)) {// 30天内的
				sql.append(" and DATEDIFF(NOW(),m.create_time)<=30 and DATEDIFF(NOW(),m.create_time)>=0");
				countsql.append(" and DATEDIFF(NOW(),m.create_time)<=30 and DATEDIFF(NOW(),m.create_time)>=0");
			} else if ("5".equals(lostTime)) {// 3个月内
				sql.append(" and DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(m.create_time)");
				countsql.append(" and DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= date(m.create_time)");
			} else if ("6".equals(lostTime)) {// 6个月内
				sql.append(" and DATE_SUB(CURDATE(), INTERVAL 6 MONTH) <= date(m.create_time)");
				countsql.append(" and DATE_SUB(CURDATE(), INTERVAL 6 MONTH) <= date(m.create_time)");
			} else if ("7".equals(lostTime)) {// 1年内
				sql.append(" and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(m.create_time)");
				countsql.append(" and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(m.create_time)");
			}

		}
		int count = queryCount(countsql.toString(), null);
		sql.append(" LIMIT ");
		sql.append((page - 1) * size);
		sql.append(",");
		sql.append(size);
		List<Message> list = queryList(sql.toString(), null);
		pageEntity.setPage(page);
		pageEntity.setSize(size);
		pageEntity.setList(list);
		pageEntity.setTotalCount(count);

		return pageEntity;

	}

	/**
	 * 添加类型
	 * 
	 * @param
	 * @return
	 */
	public int addMessage(Message message) {
		String sql = "INSERT INTO message(message.title,message.imgs,message.message_type,message.create_user,message.address,message.create_time,message.state,message.lost_time,message.detail_content) VALUES(?,?,?,?,?,now(),?,?,?)";
		List<Object> objs = new ArrayList<Object>();
		objs.add(message.getTitle());
		objs.add(message.getImgs());
		objs.add(message.getMessageType().getId());
		objs.add(message.getCreateUse().getId());
		objs.add(message.getAddress());
		objs.add(message.getState());
		objs.add(message.getLostTime());
		objs.add(message.getDetailContent());
		return AddOrRemoveOrUpdate(sql, objs);
	}

	/**
	 * 修改
	 * 
	 * @param messageType
	 * @return
	 */
	public int updateMessage(Message message) {
		String sql = "update message set message.title=?,message.imgs=?,message.message_type=?,message.create_user=?,message.address=?,message.state=?,message.lost_time=?,message.detail_content=? where message.id=?";
		List<Object> objs = new ArrayList<Object>();
		objs.add(message.getTitle());
		objs.add(message.getImgs());
		objs.add(message.getMessageType().getId());
		objs.add(message.getCreateUse().getId());
		objs.add(message.getAddress());
		objs.add(message.getState());
		objs.add(message.getLostTime());
		objs.add(message.getDetailContent());
		objs.add(message.getId());
		return AddOrRemoveOrUpdate(sql, objs);
	}
	
	/**
	 * 修改状态
	 * @param id
	 * @param state
	 * @return
	 */
	public int updateState(String id,String state){
		String sql="update message set state=? where id=?";
		List<Object> objs = new ArrayList<Object>();
		objs.add(state);
		objs.add(id);
		return AddOrRemoveOrUpdate(sql, objs);
	}

	/**
	 * 删除
	 * 
	 * @param ids
	 * @return
	 */
	public int deleteBymessageIds(String ids) {
		StringBuffer sql = new StringBuffer("DELETE FROM message WHERE id in(");
		sql.append(ids);
		sql.append(")");
		return AddOrRemoveOrUpdate(sql.toString(), null);
	}

	/**
	 * 审核消息
	 * 
	 * @param id
	 * @return
	 */
	public int auditMessage(Integer state, String id) {
		StringBuffer sql = new StringBuffer("UPDATE message SET state=? WHERE id=?");
		List<Object> objs = new ArrayList<Object>();
		objs.add(state);
		objs.add(id);
		return AddOrRemoveOrUpdate(sql.toString(), objs);
	}

	/**
	 * 查询单个消息
	 * 
	 * @param id
	 * @return
	 */
	public Message queryOne(Integer id) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("m.id,");
		sql.append("m.address,");
		sql.append("m.create_time,");
		sql.append("m.detail_content,");
		sql.append("m.imgs,");
		sql.append("m.lost_time,");
		sql.append("m.state,");
		sql.append("m.title,");
		sql.append("u.id AS u_id,");
		sql.append("u.user_name,");
		sql.append("u.pass_word,");
		sql.append("u.`name` AS u_name,");
		sql.append("u.sex,");
		sql.append("u.head_img,");
		sql.append("u.phone,");
		sql.append("u.address AS u_address,");
		sql.append("u.create_time AS u_create_time,");
		sql.append("u.is_admin,");
		sql.append("u.state AS u_state,");
		sql.append("u.province,");
		sql.append("u.city,");
		sql.append("u.area,");
		sql.append("u.age,");
		sql.append("mt.id AS t_id,");
		sql.append("mt.`name` AS t_name,");
		sql.append("pmt.id AS p_id,");
		sql.append("pmt.`name` AS p_name,");
		sql.append("pmt.type AS p_type,");
		sql.append("pmt.create_time AS p_create_time,");
		sql.append("mt.type,");
		sql.append("mt.create_time as t_create_time ");
		sql.append(
				"from message m LEFT JOIN  message_type mt ON m.message_type=mt.id LEFT JOIN message_type pmt on mt.parent_id=pmt.id LEFT JOIN user_info u on m.create_user=u.id where m.id=?");
		List<Object> objs = new ArrayList<Object>();
		objs.add(id);
		return queryOne(sql.toString(), objs);
	}

}
